Introduction to Query Performance

In database management, achieving the correct logical result is only half the goal; understanding query performance ensures that the database computes that result efficiently without wasting system resources. A query can successfully return the exact data requested while executing inefficiently behind the scenes.

Learning Objectives & Context

Lecture 11 — Correct Answer, Terrible Query: Emphasizes that writing working SQL is not enough; database developers must understand how the query optimizer executes operations under the hood.

Core Concepts

Logical Correctness vs. Execution Performance: Successfully returning data does not guarantee efficient resource utilization. Two functionally identical queries can yield the exact same output table while consuming vastly different amounts of CPU, memory, and disk I/O.
Impact on System Scalability: Unoptimized queries act as performance bottlenecks, slowing down concurrent users, increasing server load, and failing to scale gracefully as database size grows.
Example Scenario: Searching for a specific customer in a table with millions of rows. A poor query forces a full scan taking several seconds, whereas an optimized query retrieves the record in milliseconds.

Supplementary Notes

Lecture 11 — Correct Answer, Terrible Query: Database developers must analyze execution plans to ensure queries scale efficiently under heavy production workloads.

QUESTION 1

What is the core idea of “Introduction to Query Performance”?

Skip the concept and move directly to the next section.

Learners should understand why a query can produce the correct logical result while performing poorly.

QUESTION 2

Which statement is explicitly supported by the page?

Resource consumption differences among functionally identical queries

Skip the concept and move directly to the next section.

QUESTION 3

Which statement is explicitly supported by the page?

Skip the material and memorise the answer.

Impact of unoptimized queries on system scalability

Draw a conclusion from intuition without evidence or testing.

Change every factor at once when comparing results.

QUESTION 4

Which learning outcome best matches this page?

Learners should understand why a query can produce the correct logical result while performing poorly.

Draw a conclusion from intuition without evidence or testing.

Skip the material and memorise the answer.

Change every factor at once when comparing results.

QUESTION 5

Which topic does this page review?

Introduction to Query Performance

Draw a conclusion from intuition without evidence or testing.

Skip the material and memorise the answer.

Change every factor at once when comparing results.

Case Study: The E-Commerce Customer Search

Analyze query performance issues in a retail database.

An online store manages a `customers` table containing 10 million rows. A developer writes the query `SELECT * FROM customers WHERE email = '[email protected]';`. Without an index on the `email` column, the database performs a full table scan, taking 3.5 seconds and spiking CPU usage. With an appropriate index, the query executes in 2 milliseconds.

1. Why does searching without an index cause high disk I/O and CPU consumption in this scenario?

Answer:
Without an index, the database engine has no direct reference to the location of the target record. It must inspect every single data page and row in the `customers` table sequentially to verify the email address, resulting in extensive disk reads and high CPU processing time.

2. How does creating an index on the `email` column change the execution path and resolve the performance bottleneck?

Answer:
An index maintains a sorted tree structure (such as a B-tree) that maps attribute values directly to their physical storage locations on disk. This allows the database engine to perform a rapid lookup and retrieve the exact record in milliseconds without scanning unrelated rows.